﻿
SET QUOTED_IDENTIFIER ON
GO

SET ANSI_NULLS ON
GO

IF EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[fn_can_access_by_person]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
    DROP FUNCTION [dbo].[fn_can_access_by_person];
GO
CREATE FUNCTION [dbo].[fn_can_access_by_person]
(
    @Class_ID   int,    -- the id of the class to validate (UDS_Class.ClassID)
    @UserID     int,    -- the name of the user
    @Act_ID     int     -- the id of the permission (UDS_Proc.Proc_ID)
)
RETURNS BIT
AS
BEGIN

--得到个人@UserName是否能对组@ClassID进行@Act_ID操作
If EXISTS(SELECT 1 
    FROM
        UDS_Assign_Rule
    WHERE     
        Staff_ID = @UserID
        and Act_ID = @act_id 
        and Based_On = 4 /* staff */ 
        and Team_ID = 0 /* 0 indicates global class */
    ) 
BEGIN
    RETURN 1;
END

--是否可以访问指定类的父类？
IF EXISTS(
    SELECT 1 
        FROM 
            dbo.UDS_Assign_Rule
        WHERE
            Staff_ID = @UserID
            AND Act_id = @act_id
            AND based_on = 4 /* staff */
            AND Team_ID IN (
                SELECT ClassID
                FROM dbo.fn_get_parent_class(@Class_ID)
            )
)
BEGIN
    RETURN 1;
END

-- Access denied.
RETURN 0;

END
GO